package com.saic.parse.impl;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.saic.parse.IImport;


public class ImportSeries implements IImport {
	@Override
	public void importExcel(Connection conn) throws Exception {
		int flag = 0;
		Statement stmt = conn.createStatement();
		
		String url = null;
		InputStream stream = new FileInputStream("d:/series.xls");  
		stmt.execute("drop table t_series_b_x_a");
		stmt.execute("delete from t_velseries");
		stmt.execute("CREATE TABLE `t_series_b_x_a` (`name` varchar(50) DEFAULT NULL,`bitautoid` varchar(50) DEFAULT NULL," +
				" `xcarid` varchar(50) DEFAULT NULL,`autohomeid` varchar(50) DEFAULT NULL,`id` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
		

		String a_series_webid = null;		// 汽车之家车系webid

		HSSFWorkbook wb = new HSSFWorkbook(stream); 
		Sheet sheet = wb.getSheetAt(0);  
		//获取数据
		for(Row row:sheet){
			String a_brand_webid = null;		// 汽车之家主品牌webid 
			String a_brand_name = null;			// 汽车之家主品牌名
			String a_subbrand_webid = null;		// 汽车之家子品牌 webid
			String a_subbrand_name = null;		// 汽车之家子品牌名
			String a_desc = null;				// 描述
			String a_series_name = null;		// 汽车之家车系名
			
			String x_brand_webid = null;		// xcar主品牌webid
			String x_subbrand_webid = null;		// xcar子品牌webid
			String x_series_webid = null;		// xcar车系webid
			String x_series_name = null;		// xcar车系name
			
			String b_brand_webid = null;		// bitauto主品牌webid
			String b_subbrand_webid = null;		// bitauto子品牌webid
			String b_series_webid = null;		// bitauto车系webid
			String b_series_name = null;		// bitauto车系name
			for(Cell cell:row){
				
				//主品牌web_id
				if(cell.getColumnIndex() == 0){
					a_brand_webid = cell.toString();
				}
				//主品牌name
				if(cell.getColumnIndex() == 1){
					a_brand_name = cell.toString();
				}
				//子品牌webid
				if(cell.getColumnIndex() == 2){
					a_subbrand_webid = cell.toString();
				}
				//子品牌name
				if(cell.getColumnIndex() == 3){
					a_subbrand_name = cell.toString();
				}
				//汽车之家车系webid
				if(cell.getColumnIndex() == 4){
					if(cell.toString() != null && !cell.toString().trim().equals("") && !cell.toString().trim().equals("null"))
						a_series_webid = cell.toString();
				}
				//汽车之家车系名
				if(cell.getColumnIndex() == 5){
					a_series_name = cell.toString();
				}
				//易车子品牌webid
				if(cell.getColumnIndex() == 15){
					b_subbrand_webid = cell.toString();
				}
				//易车车系web_id
				if(cell.getColumnIndex() == 17){
					b_series_webid = cell.toString();
				}
				//易车车系名
				if(cell.getColumnIndex() ==18){
					b_series_name = cell.toString();
				}
				
				
				//爱卡子品牌webid
				if(cell.getColumnIndex() == 8){
					x_subbrand_webid = cell.toString();
				}				
				//爱卡车系web_id
				if(cell.getColumnIndex() == 10){
					x_series_webid = cell.toString();
				}
				//爱车车系名
				if(cell.getColumnIndex() == 11){
					x_series_name = cell.toString();
				}
				
			}
			
//			ResultSet resultSet = stmt.executeQuery("select web_id from t_bitauto_brand as brand,t_bitauto_subbrand as subbrand " +
//					"where brand.web_id=subbrand.brand_web_id and brand.name='" +a_brand_name+
//					"' and subbrand.name='"+a_subbrand_name+"'");
//			
//			while(resultSet.next()){
//				//获取bitauto子品牌id
//				b_subbrand_webid = resultSet.getString(1);
//			}

			

			
			ResultSet resultAutohomeId = stmt.executeQuery("select id from t_subbrand where autohomeid='"+a_subbrand_webid+"'");
			resultAutohomeId.next();
			String  autohomeId = resultAutohomeId.getString(1);				//车系id新的id
			
			if(a_brand_webid != null && !a_brand_webid.equals("") && a_series_webid != null && !a_series_webid.equals("") && a_series_name !=""){
				PreparedStatement pstateT = conn.prepareStatement("insert into t_velseries (name,`desc`,subbrandid) values(?,?,?)");
				pstateT.setString(1, a_series_name);
				pstateT.setString(2, a_series_name);
				pstateT.setString(3,autohomeId);
				pstateT.execute();
			}
			//新的id  根据指定表的下一个增加量
			ResultSet resultSetNew = stmt.executeQuery("select id from t_velseries order by id desc limit 1");
			resultSetNew.next();
			int newId = resultSetNew.getInt(1);				//车系id新的id
				PreparedStatement pstate = conn.prepareStatement("insert into t_series_b_x_a (name,bitautoid,xcarid,autohomeid,id) values(?,?,?,?,?)");
				pstate.setString(1, a_series_name);
				pstate.setString(2, b_series_webid);
				pstate.setString(3, x_series_webid);
				pstate.setString(4, a_series_webid);
				pstate.setInt(5, newId);
				pstate.execute();
			}
			
	}
}
